package com.gzx.bi.modular.screen.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gzx.bi.modular.screen.entity.GzxHtCallReceiptBusiness;
import com.gzx.bi.modular.screen.vo.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * bi报表持久层
 *
 * @author xbl
 */
public interface GzxBICallRecordMapper extends BaseMapper<GzxHtCallReceiptBusiness> {

    //团队坐席统计-通话总量
    @Select({
            "<script>",
            "select count(*) as callTotal, " +
                    "sum(call_duration > 0) as callCompletionTotal, " +
                    "round(sum(call_duration) / 60, 2) as totalCallDuration, " +
                    "round(sum(ring_duration) / 60, 2) as ringDuration, " +
                    "dept_id as deptId " +
                    "from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "and dept_id is not null " +
                    "group by dept_id " +
                    "</script>"
    })
    List<GzxCallTotalVo> getCallTotalStatistics(@Param("date") GzxLocalDateTimeVo date);

    //团队坐席统计-呼出通话统计
    @Select({
            "<script>",
            "select count(*) as callOutgoingTotal, " +
                    "sum(call_duration > 0) as callOutgoingCompletionTotal, " +
                    "round(sum(call_duration) / 60, 2) as totalCallOutgoingDuration, " +
                    "round(sum(ring_duration) / 60, 2) as callOutgoingRingDuration, " +
                    "dept_id as deptId " +
                    "from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "and call_type != 0 " +
                    "and dept_id is not null " +
                    "group by dept_id " +
                    "</script>"
    })
    List<GzxCallOutgoingVo> getCallCompletionStatistics(@Param("date") GzxLocalDateTimeVo date);

    //历史接通分析 - 活跃线路呼出总数统计 limit太多存不进去表，40条之外的数据量很小，就算统计出来接通率太低或者数量太少也会被过滤掉
    @Select({
            "<script>",
            "select caller as lineName,count(*) as callTotal,sum(call_duration > 0) as callCompletionTotal from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "group by lineName " +
                    "order by callTotal desc " +
                    "limit 40" +
                    "</script>"
    })
    List<GzxActiveLineVo> getHistoryAnswerByTime(@Param("date") GzxLocalDateTimeVo date);

    //查询呼叫总数
    @Select({
            "<script>",
            "select count(*) total from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "</script>"
    })
    int selectCallTotalByTime(@Param("date") GzxLocalDateTimeVo date);

    //获取当天线路数量
    @Select({
            "<script>",
            "select count(distinct caller) total from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "</script>"
    })
    int getTodayCallLineNum(@Param("date") GzxLocalDateTimeVo date);

    //获取当天客户地区数量
    @Select({
            "<script>",
            "select count(distinct substring(area,1,3)) total from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "</script>"
    })
    int getTodayCallCustomerAreaNum(@Param("date") GzxLocalDateTimeVo date);

    //通话总时长
    @Select({
            "<script>",
            "select ifnull(sum(call_duration),0) total from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "</script>"
    })
    int getTodayCallTotalCallDuration(@Param("date") GzxLocalDateTimeVo date);

    //呼叫总数
    @Select({
            "<script>",
            "select ifnull(sum(call_duration > 0),0) total from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "and call_type != 0 " +
                    "</script>"
    })
    int getTodayCallNum(@Param("date") GzxLocalDateTimeVo date);

    //通话趋势分析 - 通话总量统计
    @Select({
            "<script>",
            "select count(*) total," +
                    "sum(call_duration > 0) completionTotal," +
                    "round(sum(call_duration) / 60, 2) totalCallDuration," +
                    "round(sum(ring_duration)  / 60, 2) ringDuration " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "and call_type != 0 " +
                    "</script>"
    })
    GzxCallSummaryVo getCallSummary(@Param("date") GzxLocalDateTimeVo date);

    //获取呼出数量
    @Select({
            "<script>",
            "select count(*) callTotal," +
                    "sum(call_duration > 0) callCompletionTotal," +
                    "round(sum(call_duration) / 60, 2) callDurationOutgoing," +
                    "count(distinct agent_num) activeSeatsNum " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "and call_type != 0 " +
                    "</script>"
    })
    GzxMonthCallVo getDayCallOutByTime(@Param("date") GzxLocalDateTimeVo date);

    //获取呼入数量
    @Select({
            "<script>",
            "select count(*) totalIncomingCall " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "and call_type = 0 " +
                    "</script>"
    })
    GzxMonthCallVo getDayCallInByTime(@Param("date") GzxLocalDateTimeVo date);

    //获取所有通话数量
    @Select({
            "<script>",
            "select count(*) total," +
                    "round(sum(call_duration) / 60, 2) totalCallDuration" +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "</script>"
    })
    GzxMonthCallVo getDayAllByTime(@Param("date") GzxLocalDateTimeVo date);

    //通话趋势分析 - 呼叫类型分布
    @Select({
            "<script>",
            "select call_type as name , count(*) as value from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "group by name " +
                    "</script>"
    })
    List<GzxCommonVo> getCallTypeDistribution(@Param("date") GzxLocalDateTimeVo date);

    //通话趋势分析 - 呼出结果分布
    @Select({
            "<script>",
            "select asr_name as name , count(*) as value from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "group by name " +
                    "</script>"
    })
    List<GzxCommonVo> getCallResultDistribution(@Param("date") GzxLocalDateTimeVo date);

    //每天调解中心的通话时长
    @Select({
            "<script>",
            "select round(sum(call_duration) / 60, 2) as totalCallDuration," +
                    " dept_id as deptId " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " group by deptId " +
                    "</script>"
    })
    List<GzxOfficeVo> getOfficeDuration(@Param("date") GzxLocalDateTimeVo date);

    //每天top10调解员的通话时长
    @Select({
            "<script>",
            "select round(sum(call_duration) / 60, 2) as callDuration," +
                    " agent_num as agentId " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " group by agentId " +
                    " order by callDuration desc " +
                    " limit 10 " +
                    "</script>"
    })
    List<GzxAgentCallVo> getAgentDuration(@Param("date") GzxLocalDateTimeVo date);

    //每天所有地区的呼叫量
    @Select({
            "<script>",
            "select count(*) as value," +
                    " substring(area,1,3) as name " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " and area is not null " +
                    " group by name " +
                    "</script>"
    })
    List<GzxCommonVo> getCallByArea(@Param("date") GzxLocalDateTimeVo date);

    //历史接通分析 - 当天呼叫统计
    @Select({
            "<script>",
            "select count(*) as callTotal," +
                    " sum(call_duration > 0) as callCompletionTotal " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " and call_type != 0 " +
                    "</script>"
    })
    GzxCallStatisticVo getCallStatistic(@Param("date") GzxLocalDateTimeVo date);

    //历史接通分析 - 客户分布统计
    @Select({
            "<script>",
            "select count(*) as callTotal," +
                    " sum(call_duration > 0) as callCompletionTotal," +
                    " substring(area,1,3) as areaName " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " and area is not null " +
                    " and call_type != 0 " +
                    " group by areaName " +
                    "</script>"
    })
    List<GzxCustomerDistributionVo> getCustomerDistribution(@Param("date") GzxLocalDateTimeVo date);

    //接通总数，接通总时长（分钟）
    @Select({
            "<script>",
            "select count(*) as callTotal," +
                    " round(sum(call_duration) / 60, 2) as totalCallDuration" +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " and call_duration > 0 " +
                    "</script>"
    })
    GzxMainBIVo getCallComplete(@Param("date") GzxLocalDateTimeVo date);

    //呼出总数，活跃坐席数，人均呼叫=呼出总数/活跃坐席数
    @Select({
            "<script>",
            "select count(*) as callOutgoingTotal," +
                    " count(distinct agent_num) activeAgentTotal " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " and call_type != 0 " +
                    "</script>"
    })
    GzxMainBIVo getCallOutActiveSeat(@Param("date") GzxLocalDateTimeVo date);

    //团队人均呼叫量排行榜
    @Select({
            "<script>",
            "select count(*) as callTotalRank," +
                    " dept_id as deptId " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " group by deptId " +
                    "</script>"
    })
    List<GzxOfficeCallRankVo> getTeamAgent(@Param("date") GzxLocalDateTimeVo date);

    //坐席呼叫量排行榜
    @Select({
            "<script>",
            "select count(*) as callTotalRank," +
                    " agent_num as agentId," +
                    " dept_id as deptId " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " group by agentId,deptId " +
                    " order by callTotalRank desc " +
                    " limit 30 " +
                    "</script>"
    })
    List<GzxAgentCallRankVo> getAgentCallRank(@Param("date") GzxLocalDateTimeVo date);

    //客户分布地区
    @Select({
            "<script>",
            "select count(*) as areaCustomerNum," +
                    " substring(area,1,3) as areaName " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    " and area is not null " +
                    " group by areaName " +
                    "</script>"
    })
    List<GzxAreaVo> getAreaByDay(@Param("date") GzxLocalDateTimeVo date);

    //人均载案量(打通的就算)
    @Select({
            "<script>",
            "select ifnull(sum(call_duration > 0),0) total " +
                    " from gzx_ht_call_receipt_business " +
                    "where created_time between #{date.startDate} and #{date.endDate} " +
                    "</script>"
    })
    int getAverageCallTotal(@Param("date") GzxLocalDateTimeVo date);
}
